﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SupermarketSalesModel;
using System.Data.SqlClient;

namespace pe.edu.pucp.lp2.sales.dao
{
    public abstract class DAOFactory
    {
        public const int MYSQL = 1;
        public const int SQLSERVER = 2;
        public const int XML = 3;

        public static DAOFactory getDAOFactory(int dbType)
        {
            DAOFactory factory = null;
            switch (dbType)
            {
                //case MYSQL: factory = new MySQLDAOFactory(); break;
                case SQLSERVER: factory = new SQLServerDAOFactory(); break;
                //case XML: factory = new XMLDAOFactory(); break;
            }
            return factory;
        }

        public abstract DAOProduct getDAOProduct();
        public abstract DAOCustomer getDAOCustomer();

    }

    public class SQLServerDAOFactory : DAOFactory
    {

        public override DAOProduct getDAOProduct()
        {
            return new SQLServerDAOProduct();
        }

        public override DAOCustomer getDAOCustomer()
        {
            return new SQLServerDAOCustomer();
        }

    }


    public interface DAOProduct
    {
        void add(Product p);
        void update(Product p);
        void delete(int productId);
        List<Product> queryAll();
        Product queryById(int productId);
        int count();
    }

    public interface DAOCustomer
    {

    }

    public class SQLServerDAOCustomer : DAOCustomer
    {

    }

    public class SQLServerDAOProduct : DAOProduct
    {

        public void add(Product p)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Product(name, description, price," +
                    "stock, brand) VALUES (@param1,@param2,@param3,@param4,@param5)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.VarChar);
                param1.Value = p.getName();
                param2.Value = p.getDescription();
                param3.Value = p.getBasePrice();
                param4.Value = p.getStock();
                param5.Value = p.getBrand();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void update(Product p)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE Product SET name=@param1, description=@param2," +
                        "price=@param3, stock=@param4, brand=@param5 WHERE id=@param6";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param6 = new SqlParameter("@param6",
                                                        System.Data.SqlDbType.Int);

                param1.Value = p.getName();
                param2.Value = p.getDescription();
                param3.Value = p.getBasePrice();
                param4.Value = p.getStock();
                param5.Value = p.getBrand();
                param6.Value = p.getId();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);
                comm.Parameters.Add(param6);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void delete(int productId)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM Product " +
                                            " WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = productId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public List<Product> queryAll()
        {
            List<Product> list = new List<Product>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Product";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    Product p = new Product();
                    p.setId(Int32.Parse(myReader["Id"].ToString()));
                    p.setName(myReader["name"].ToString());
                    p.setDescription(myReader["description"].ToString());
                    p.setBasePrice(Double.Parse(myReader["price"].ToString()));
                    p.setStock(Int32.Parse(myReader["stock"].ToString()));
                    p.setBrand(myReader["brand"].ToString());
                    list.Add(p);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }

        public Product queryById(int productId)
        {
            Product p = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Product WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = productId;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    p = new Product();
                    p.setId(Int32.Parse(myReader["Id"].ToString()));
                    p.setName(myReader["name"].ToString());
                    p.setDescription(myReader["description"].ToString());
                    p.setBasePrice(Double.Parse(myReader["price"].ToString()));
                    p.setStock(Int32.Parse(myReader["stock"].ToString()));
                    p.setBrand(myReader["brand"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return p;
        }

        public int count()
        {
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM Product";
                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
        }

    }


}
